# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/9/20 14:03'

'''
客户经营情况报表
功能：
   将月数据统计表进行合并，产生大数据统计表，写成excel文件，发送邮件

客户经营情况报表

表结构：
   客户经营情况报表  ---  k3jyqks
   
计算时间点：
    月底（最后半天）计算本月所有数据

处理过程：
  1. 由月份获取当月的合并数据，返回dataframe数据集
  2. 将dataframe数据集直接写excel
  3. 发邮件(见common模块中的email_helper.py)

'''
#导入依赖库
from common.pandas_helper import PandasHelper
from common.utils import FVDateTime, get_month, series_value,increase_rate
import pandas as pd
from common.logger_helper import g_wlogger
from common.email_helper import email_send_with_appendix_list
from common.mssql_helper import g_msql_inst
from k3_data_report.finance.utils import fetch_lastyear_ljqk

CHTJ_DICT = {
    'k3jyqks':"select   ymonth, scode, kehuname, depart, khname, kuanqi, paytype, mhetonge, monthml, ycke, ymaoli, "
              "yske, skyml, myysk, yfdqysk, yfcqys, yfykfp    from k3jyqks  where  paytype='%s' and ymonth  BETWEEN '%s' and '%s'",
    'k3_cpgly':"select   gys, cpgly   from k3_cpgly order by sjsj desc",
}


def generate_sql(type, begin_time, end_time, paytype='人民币'):
    target_tb_sql = CHTJ_DICT[type] % (paytype, begin_time, end_time)
    return target_tb_sql


def create_df_k3jyqks(month=None, paytype='人民币'):
    '''
    客户经营情况报表
    :param month: 输入的月份，默认是本月
    :return:
    '''
    #查询品牌，产品经理，产品助理，当月出库额，时间范围为：当年上月，去年同月，当年当前季度，去年同季度
    if month == None:
        month = get_month(FVDateTime().to_dict()['this_month_start'])

    the_year = int(month.split("-")[0])
    fvdate_dict = FVDateTime(month).to_dict()

    #本年度数据，获取所有的从今年1月份到现在本月的时间数据
    this_year_start = fvdate_dict['this_year_start']
    this_month_end = fvdate_dict['this_month_end']

    target_tb_sql = generate_sql("k3jyqks", this_year_start, this_month_end, paytype)
    target_full_df = PandasHelper.pd_query_sql(target_tb_sql)
    if target_full_df.empty:
        g_wlogger.werror("create_df_k3jyqks:target_full_df, all data is empty.")
        return pd.DataFrame()

    target_full_df[['mhetonge', 'monthml', 'ycke', 'ymaoli', 'yske', 'skyml', 'myysk', 'yfdqysk', 'yfcqys', 'yfykfp']] = target_full_df[['mhetonge', 'monthml', 'ycke', 'ymaoli', 'yske', 'skyml', 'myysk', 'yfdqysk', 'yfcqys', 'yfykfp']].apply(pd.to_numeric)

    #上年累计欠款
    lastyear_qk_dict = fetch_lastyear_ljqk()

    #今年本月
    this_month = get_month(this_month_end)
    #查询本年度本月的
    month_cond = f'ymonth==["{this_month}"]'
    this_year_this_month_df = PandasHelper.df_query(target_full_df, month_cond)
    #判断this_year_this_month_df是否能查询到，不为空，分组计算，否则为None
    #bydde,  byrke,byyfk, byyinfk,  byysp, wsfp, xyyjyfk
    this_year_this_month_group_df = this_year_this_month_df.groupby('scode')['mhetonge','monthml','ycke','ymaoli','yske', 'skyml','myysk',
                'yfdqysk','yfcqys','yfykfp'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    #本年到目前为止的金额统计
    # 查询本年度本月的
    this_year_start_month = get_month(this_year_start)
    month_cond = (target_full_df['ymonth'] >= this_year_start_month) & (
                target_full_df['ymonth'] <= this_month)
    this_year_this_month_df = PandasHelper.df_cond_filter(target_full_df, month_cond)
    # bydde,  byrke,byyfk, byyinfk,  byysp, wsfp, xyyjyfk
    this_year_group_df = this_year_this_month_df.groupby('scode')['mhetonge','monthml','ycke','ymaoli','yske', 'skyml','myysk',
                                                    'yfdqysk','yfcqys','yfykfp'].sum() if not this_year_this_month_df.empty else pd.DataFrame()

    #获取基础信息
    #scode, kehuname, depart, khname, kuanqi, paytype
    target_chtjcp_base_df = target_full_df[['scode','kehuname', 'depart', 'khname', 'kuanqi', 'paytype']].drop_duplicates()   #获取待分析的品牌，产品经理，产品助理
    target_code_series = target_full_df['scode'].drop_duplicates()    #获取唯一参考
    df_data_list = []
    for scode  in  target_code_series:
        base_df = target_chtjcp_base_df.query(f'scode==["{scode}"]')
        kehuname = base_df['kehuname'].values[0]
        depart = base_df['depart'].values[0]
        khname = base_df['khname'].values[0]
        kuanqi = base_df['kuanqi'].values[0]
        paytype = base_df['paytype'].values[0]
        #获取本年本月合同额
        this_year_this_month_mhetonge = 0  if this_year_this_month_group_df.empty else this_year_this_month_group_df['mhetonge'].get(scode,0)
        this_year_mhetonge = 0 if this_year_group_df.empty else this_year_group_df['mhetonge'].get(scode,0)

        # 获取本月合同毛利
        this_year_this_month_monthml =  0   if   this_year_this_month_group_df.empty else   this_year_this_month_group_df['monthml'].get(scode,0)
        this_year_monthml  = 0 if this_year_group_df.empty else this_year_group_df['monthml'].get(scode,0)

        # 获取本月出库额
        this_year_this_month_ycke = 0 if this_year_this_month_group_df.empty else \
                            this_year_this_month_group_df['ycke'].get(scode,0)
        this_year_ycke = 0 if this_year_group_df.empty else this_year_group_df['ycke'].get(scode,0)


        # 获取本月出库毛利
        this_year_this_month_ckymaoli= 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['ymaoli'].get(scode,0)
        this_year_ckymaoli = 0 if this_year_group_df.empty else this_year_group_df['ymaoli'].get(scode,0)

        # 获取本月收款额
        this_year_this_month_yske = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yske'].get(scode,0)
        this_year_yske = 0 if this_year_group_df.empty else this_year_group_df['yske'].get(scode,0)


        # 获取本月收款额毛利
        this_year_this_month_skyml = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['skyml'].get(scode,0)
        this_year_skyml = 0 if this_year_group_df.empty else this_year_group_df['skyml'].get(scode,0)


        # 获取本月应收款
        this_year_this_month_myysk = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['myysk'].get(scode,0)
        this_year_myysk = 0 if this_year_group_df.empty else this_year_group_df['myysk'].get(scode,0)


        # 获取到期应收款
        this_year_this_month_yfdqysk = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yfdqysk'].get(scode,0)
        this_year_yfdqysk = 0 if this_year_group_df.empty else this_year_group_df['yfdqysk'].get(scode,0)


        # 获取超期应收款
        this_year_this_month_yfcqys = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yfcqys'].get(scode,0)
        this_year_yfcqys = 0 if this_year_group_df.empty else this_year_group_df['yfcqys'].get(scode,0)

        #应开发票
        this_year_this_month_yfykfp = 0 if this_year_this_month_group_df.empty else \
            this_year_this_month_group_df['yfykfp'].get(scode,0)
        this_year_yfykfp = 0 if this_year_group_df.empty else this_year_group_df['yfykfp'].get(scode,0)

        #上年末未收款,期初未收款
        # 上年年末欠款
        last_year_total_wsk = lastyear_qk_dict.get(scode, 0)
        if last_year_total_wsk != 0:
            last_year_total_wsk = last_year_total_wsk[1] if the_year == 2019 else last_year_total_wsk[0]

        #期末累计未收款 = 期初未收款 +累计出库 - 本年累计已收款
        year_qmljwsk = "%.2f" %  (float(last_year_total_wsk) + this_year_ycke - this_year_yske)

        #TODO 银行承兑票
        kh_yhcdpje = 0

        #如果一条记录中所有的年度累计金额都为空，则跳过
        if (this_year_mhetonge == 0) and (this_year_yske == 0) and (this_year_myysk == 0) and (this_year_yfcqys == 0) and (this_year_yfykfp ==0):
            continue
        #'scode','kehuname', 'depart', 'khname', 'kuanqi', 'paytype'
        item_tuple = (scode, kehuname, depart, khname, kuanqi, last_year_total_wsk,
                      this_year_mhetonge,this_year_monthml,this_year_this_month_mhetonge,this_year_this_month_monthml,this_year_ycke,this_year_ckymaoli,
                      this_year_this_month_ycke,this_year_this_month_ckymaoli,year_qmljwsk,kh_yhcdpje,this_year_yske,this_year_skyml,this_year_this_month_yske,
                      this_year_this_month_skyml, this_year_this_month_myysk, this_year_this_month_yfdqysk, this_year_this_month_yfcqys, this_year_this_month_yfykfp)
        df_data_list.append(item_tuple)


    #说明：此处的columns列一定要和上述item_tuple顺序对应上
    columns = ["序号", "客户名称", "部门", "客户经理", "款期", "2018年年未收款",
               "本年累计有效合同额", "本年累计合同毛利", "本月合同额", "本月合同毛利", "本年累计出库额","本年累计出库毛利",
               "本月出库额", "本月出库毛利", "期末累计未收款", "银行承兑汇票", "累计已收款", "累计收款毛利","本月收款额",
               "本月收款毛利", "本月应收款", "下月到期应收", "下月超期应收", "下月应开发票"]

    k3jyqks_df = PandasHelper.create_dataframe(data=df_data_list, columns=columns)

    return k3jyqks_df



def create_df_k3jyqks_rmb(month, paytype='人民币'):
    #paytype = '人民币'
    k3jyqks_rmb_df = create_df_k3jyqks(month, paytype=paytype)
    if k3jyqks_rmb_df.empty:
        g_wlogger.werror(f"create_df_k3jyqks_rmb:create_df_k3cgfkds_rmb {paytype} return empty, please check.")
    return k3jyqks_rmb_df


def create_df_k3jyqks_mj(month, paytype='美金'):
    k3jyqks_mj_df = create_df_k3jyqks(month, paytype=paytype)
    if k3jyqks_mj_df.empty:
        g_wlogger.werror(f"create_df_k3jyqks_mj:k3jyqks_mj_df {paytype} return empty, please check.")
    return k3jyqks_mj_df


def  k3jyqks_dp_main(month):
    #1. 由月份获取当月的合并数据，返回dataframe数据集
    #人民币
    paytype = '人民币'
    k3jyqks_rmb_df = create_df_k3jyqks(month, paytype=paytype)
    if k3jyqks_rmb_df.empty:
        g_wlogger.werror(f"k3jyqks_dp_main:create_df_k3jyqks  rmb {paytype} return empty, please check.")
        return

    #2. 写excel
    PandasHelper.df_to_excel(k3jyqks_rmb_df, f"k3jyqks_{paytype}.xls")

    paytype='美金'
    k3jyqks_rmb_df = create_df_k3jyqks(month, paytype=paytype)
    if k3jyqks_rmb_df.empty:
        g_wlogger.werror("k3jyqks_dp_main:create_df_k3jyqks mj {paytype}  return empty, please check.")
        return
    # 2. 写excel
    PandasHelper.df_to_excel(k3jyqks_rmb_df, f"k3jyqks_{paytype}.xls")

    #3. 发送邮件
    subject = f"财务统计报表-{month}"  #邮件主题
    mail_msg = "财务统计报表-客户经营情况报表"  #邮件主体部分内容，可以写成html格式
    appendix_files = ["k3jyqks_人民币.xls", "k3jyqks_美金.xls"]
    email_send_with_appendix_list(subject, mail_msg, appendix_files)


if __name__ == "__main__":
    month = '2019-07'

    k3jyqks_dp_main(month)







